<?php

// $Id$

require_once(TANGRA_MAIN_DIR.'interfaces/i_db_storable.class.php');


require_once('tcms_ci_attachment.class.php');


class TCMS_CI_Attachment_DBC extends TCMS_CI_Attachment implements I_DB_Storable {
	public function save(DB_Connection $dbc) {
		$ret = false;

		if ($this->get_id()) {
			$ret = $this->update($dbc);
		} else {
			$ret = $this->insert($dbc);
			$this->set_id($ret);
		}

		return $ret;
	}


	public function load_by_id(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$ret = false;

		$sql = "select ci, attachment, note from tcms_ci_attachments where id = $id";
		$rez = $dbc->execute($sql);

		if (!$rez->is_eof()) {
			$rez_obj = $rez->fetch_object();

			$this->set_id($id);
			$this->set_ci($rez_obj->CI);
			$this->set_attachment($rez_obj->ATTACHMENT);
			$this->set_note(stripslashes($rez_obj->NOTE));

			$ret = $id;
		}

		return $ret;
	}


	protected function insert(DB_Connection $dbc) {
		$ret = false;

		$id = $dbc->generate_id('tcms_ci_attachments_seq');
		$ci = $this->get_ci();
		$attachment = $this->get_attachment();
		$note = addslashes($this->get_note());

		if ($id) {
			$sql = "insert into tcms_ci_attachments (id, ".
									      "ci, ".
									      "attachment, ".
									      "note ".
									      ") ".
								"values ".
									      "($id,".
									      "$ci, ".
									      "$attachment, ".
									      "'$note' ".
									      ")";
			$dbc->execute($sql);
			$ret = $id;
		} else {
			throw new TE_Exception('tcms_ci_attachments_seq');
		}

		return $ret;
	}


	protected function update(DB_Connection $dbc) {
		$id = $this->get_id();

		$ci = $this->get_ci();
		$attachment = $this->get_attachment();
		$note = addslashes($this->get_note());

		$sql = "update tcms_ci_attachments set ".
						"ci = $ci, ".
						"attachment = $attachment, ".
						"note = '$note' ".
					"where id = ".$id;

		$dbc->execute($sql);
		$ret = $id;

		return $ret;
	}


	public static function delete(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$sql = "delete from tcms_ci_attachments where id = $id";
		$dbc->execute($sql);
	}


	public static function get_sql_for_grid($ci_id) {
		$sql = "select ca.id, "
						."i.orig_filename, "
						."i.size, "
						."i.note, "
						."ca.id, "
						."ca.note "
					."from tcms_ci_attachments ca, tcms_attachments i "
					."where ca.ci = $ci_id "
						." and ca.attachment = i.id "
					."order by ca.id desc";

		return $sql;
	}


	public static function	get_sql_count_for_grid($ci_id) {
		$sql = "select count(id) as total_rows from tcms_ci_attachments where ci = $ci_id";

		return $sql;
	}



	public static function get_sql_for_cas_grid($ci_id, $filename, $note) {
		$where = self::prepare_where_clause($filename, $note);
		$sql = "select a.id, "
						."a.orig_filename, "
						."a.size, "
						."a.filename, "
						."a.note, "
						."ca.id, "
						."ca.note "
					."from tcms_attachments a left outer join tcms_ci_attachments ca on (a.id = ca.attachment and ca.ci = $ci_id) ".$where
					." order by a.orig_filename desc";

		return $sql;
	}


	public static function get_sql_count_for_cas_grid($filename, $note) {
		$where = self::prepare_where_clause($filename, $note);
		$sql = "select count(id) as total_rows from tcms_attachments";

		return $sql;
	}


	public static function exists(DB_Connection $dbc, $ci_id, $attachment_id) {
		tangra_if_not_int_throw_e($ci_id);
		tangra_if_not_int_throw_e($attachment_id);

		$sql = "select id from tcms_ci_attachments where ci = $ci_id and attachment = $attachment_id ".$where;
		$rez = $dbc->execute($sql);

		return !$rez->is_eod();
	}


	public static function select_ci_ids_and_type_for_attachment(DB_Connection $dbc, $attachment_id) {
		tangra_if_not_int_throw_e($attachment_id);

		$ret = array();

		$sql = "select c.id as id, c.type as type from tcms_cis c, tcms_ci_attachments ca where ca.attachment = $attachment_id and ca.ci = c.id";
		$rez = $dbc->execute($sql);

		while($rez_obj = $rez->fetch_object()) {
			$ret[$rez_obj->ID] = $rez_obj->TYPE;
		}

		return $ret;
	}


	public static function load_ci_attachments(DB_Connection $dbc, $ci_id) {
		tangra_if_not_int_throw_e($ci_id);

		$ret = array();

		$sql = "select id from tcms_ci_attachments where ci = $ci_id";

		$rez = $dbc->execute($sql);
		while($rez_obj = $rez->fetch_object()) {
			$tmp = new TCMS_ci_Attachment_DBC();
			$tmp->load_by_id($dbc, $rez_obj->ID);

			$ret[] = clone $tmp;
		}

		return $ret;
	}


	public static function delete_for_attachment(DB_Connection $dbc, $attachment_id) {
		tangra_if_not_int_throw_e($attachment_id);
		$sql = "delete from tcms_ci_attachments where attachment = $attachment_id";

		$dbc->execute($sql);
	}


	public static function delete_for_ci(DB_Connection $dbc, $ci_id) {
		tangra_if_not_int_throw_e($ci_id);

		$sql = "delete from tcms_ci_attachments where ci = $ci_id";
		$dbc->execute($sql);
	}


	private static function prepare_where_clause($filename, $note) {
		$filename = self::remove_duplicate_asteriks($filename);
		$note = self::remove_duplicate_asteriks($note);

		if ($filename || $note) {
			$where = '';
			if ($filename) {
				if (strpos($filename, '*') !== false) {
					$filename = addslashes($filename);
					$filename = str_replace('*', '%', $filename);
					$where .= "a.orig_filename like '$filename' ";
				} else {
					$filename = addslashes($filename);
					$where .= "a.orig_filename = '$filename' ";
				}
			}

			if ($note) {
				if (strpos($note, '*') !== false) {
					$note = addslashes($note);
					$note = str_replace('*', '%', $note);
					if ($where) {
						$where .= "and ";
					}
					$where .= "(a.note like '$note')";
				} else {
					if ($where) {
						$where .= "and ";
					}
					$note = addslashes($note);
					$where .= "a.note = '$note'";
				}
			}
		} else {
			$where = '';
		}

		if ($where) {
			$where = " where ".$where;
		}

		return $where;
	}


	private static function remove_duplicate_asteriks($filter) {
		$ret = $filter;
		while(strpos($ret, '**') !== false) {
			$ret = str_replace('**', '*', $ret);
		}

		return $ret;
	}
}